<?php
/**
* @file $Id: Schedule.php 577 2007-06-05 08:15:13Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

// TABBED FY,SEM,QTR
// REPLACE DBDate() & date() WITH USER ENTERED VALUES
// ERROR HANDLING
if($_REQUEST['modfunc']!='XMLHttpRequest')
	DrawHeader(ProgramTitle());

/*Widgets('activity');
Widgets('course');	
Widgets('request');
*/
Widgets('all');
Search('student_id',$extra);

if($_REQUEST['month_date'] && $_REQUEST['day_date'] && $_REQUEST['year_date'])
{
	while(!VerifyDate($date = $_REQUEST['day_date'].'-'.$_REQUEST['month_date'].'-'.$_REQUEST['year_date']))
		$_REQUEST['day_date']--;
}
else
{
	$min_date = DBGet(DBQuery("SELECT min(SCHOOL_DATE) AS MIN_DATE FROM ATTENDANCE_CALENDAR WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
	if($min_date[1]['MIN_DATE'] && DBDate('postgres')<$min_date[1]['MIN_DATE'])
		$date = $min_date[1]['MIN_DATE'];
	else
		$date = DBDate();
}

if($_REQUEST['month_schedule'] && $_POST['month_schedule'])
{
	foreach($_REQUEST['month_schedule'] as $id=>$columns)
	{
		foreach($columns as $column=>$value)
		{
			$_REQUEST['schedule'][$id][$column] = $_REQUEST['day_schedule'][$id][$column].'-'.$value.'-'.$_REQUEST['year_schedule'][$id][$column];
			if($_REQUEST['schedule'][$id][$column]=='--')
				$_REQUEST['schedule'][$id][$column] = '';
		}
	}
	unset($_REQUEST['month_schedule']);
	unset($_REQUEST['day_schedule']);
	unset($_REQUEST['year_schedule']);
	unset($_SESSION['_REQUEST_vars']['month_schedule']);
	unset($_SESSION['_REQUEST_vars']['day_schedule']);
	unset($_SESSION['_REQUEST_vars']['year_schedule']);
	$_POST['schedule'] = $_REQUEST['schedule'];
}

if($_REQUEST['schedule'] && $_POST['schedule'])
{
	foreach($_REQUEST['schedule'] as $id=>$columns)
	{
		$current_RET = DBGet(DBGet("SELECT COURSE_PERIOD_ID FROM SCHEDULE WHERE ID='".$id."'"));
		$course_period_id = $current_RET[1]['COURSE_PERIOD_ID'];
		if($columns['COURSE_PERIOD_ID'])
		{
			if($columns['COURSE_PERIOD_ID']!=$course_period_id)
			{
				DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS+1 WHERE COURSE_PERIOD_ID='".$columns['COURSE_PERIOD_ID']."'");
				DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS-1 WHERE COURSE_PERIOD_ID='".$course_period_id."'");
			}			
		}
		if($columns['MARKING_PERIOD_ID'])
			$columns['MP'] = GetMPTable(GetMP($columns['MARKING_PERIOD_ID'],'TABLE'));
		$sql = "UPDATE SCHEDULE SET ";
						
		foreach($columns as $column=>$value)
		{
			$sql .= $column."='".str_replace("\'","''",$value)."',";
		}
		$sql = substr($sql,0,-1) . " WHERE STUDENT_ID='".UserStudentID()."' AND ID='".$id."'";
		DBQuery($sql);

		if($columns['START_DATE'] || $columns['END_DATE'])
		{
			$start_end_RET = DBGet(DBQuery("SELECT ID,START_DATE,END_DATE FROM SCHEDULE WHERE STUDENT_ID='".UserStudentID()."' AND ID='".$id."' AND END_DATE<START_DATE"));
			if(count($start_end_RET))
			{
				DBQuery("DELETE FROM SCHEDULE WHERE STUDENT_ID='".UserStudentID()."' AND ID='".$id."' AND END_DATE IS NOT NULL AND END_DATE<START_DATE");
				DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS-1 WHERE COURSE_PERIOD_ID='".$course_period_id."'");
			}
		}
		// User should be asked if he wants absences to be deleted
		if($columns['END_DATE'])
			DBQuery("DELETE FROM ATTENDANCE_PERIOD WHERE STUDENT_ID='".UserStudentID()."' AND COURSE_PERIOD_ID='".$course_period_id."' AND SCHOOL_DATE > '".$columns['END_DATE']."'");
	}
	unset($_SESSION['_REQUEST_vars']['schedule']);
	unset($_REQUEST['schedule']);
}

if($_REQUEST['modfunc']=='student_drop')
{
	$cp_RET = DBGet(DBQuery("SELECT cp.MP,cp.MARKING_PERIOD_ID,c.TITLE,c.COURSE_ID,cp.COURSE_WEIGHT,cp.DAYS,cp.PERIOD_ID,cp.TEACHER_ID,cp.MARKING_PERIOD_ID,COALESCE(cp.TOTAL_SEATS,0) AS TOTAL_SEATS,COALESCE(cp.FILLED_SEATS,0) AS FILLED_SEATS FROM COURSE_PERIODS cp, COURSES c WHERE cp.COURSE_ID=c.COURSE_ID AND cp.COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'"));
	$message = '<TABLE>
	<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Course').':</font></TD><TD>'.$cp_RET[1]['TITLE'].' - '.$cp_RET[1]['COURSE_WEIGHT'].'</TD></TR>
	<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Period').':</font></TD><TD>'.GetPeriod($cp_RET[1]['PERIOD_ID']).'</TD></TR>
	<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Instructor').':</font></TD><TD>'.GetTeacher($cp_RET[1]['TEACHER_ID']).'</TD></TR>';
	if($cp_RET[1]['ROOM'])
		$message .= '<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Room').'</font></TD><TD>'.$cp_RET[1]['ROOM'].'</TD></TR>';
	$message .= '</TABLE>';
	if(Prompt(_('Confirm Drop'),_('Are you sure you want to drop this course?'),$message))
	{
		DBQuery("DELETE FROM SCHEDULE WHERE ID='".$_REQUEST['id']."'");
		unset($_REQUEST['modfunc']);
	}
}

if($_REQUEST['modfunc']=='student_add')
{
	$min_date = DBGet(DBQuery("SELECT min(SCHOOL_DATE) AS MIN_DATE FROM ATTENDANCE_CALENDAR WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
	if($min_date[1]['MIN_DATE'] && DBDate('postgres')<$min_date[1]['MIN_DATE'])
		$date = $min_date[1]['MIN_DATE'];
	else
		$date = DBDate();
	
	$cp_RET = DBGet(DBQuery("SELECT c.PREREQUISITES,cp.MP,cp.MARKING_PERIOD_ID,c.TITLE,c.COURSE_ID,cp.COURSE_WEIGHT,cp.DAYS,cp.PERIOD_ID,cp.TEACHER_ID,cp.MARKING_PERIOD_ID,cp.TOTAL_SEATS,COALESCE(cp.FILLED_SEATS,0) AS FILLED_SEATS FROM COURSE_PERIODS cp, COURSES c WHERE cp.COURSE_ID=c.COURSE_ID AND cp.COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'"));
	if(is_numeric($cp_RET[1]['TOTAL_SEATS']) && $cp_RET[1]['TOTAL_SEATS']<=$cp_RET[1]['FILLED_SEATS'])
		$warnings[] = _('That section is already full.');
	
	$current_RET = DBGet(DBQuery("SELECT COURSE_PERIOD_ID FROM SCHEDULE WHERE STUDENT_ID='".UserStudentID()."' AND COURSE_ID='".$cp_RET[1]['COURSE_ID']."' AND COURSE_WEIGHT='".$cp_RET[1]['COURSE_WEIGHT']."' AND (START_DATE<='".DBDate()."' OR (END_DATE IS NOT NULL AND END_DATE<='".DBDate()."'))"));
	if(count($current_RET))
		$warnings[] = _('You are already scheduled into this course.');

	$days_conflict = false;
	$period_RET = DBGet(DBQuery("SELECT cp.DAYS FROM SCHEDULE s,COURSE_PERIODS cp WHERE cp.COURSE_PERIOD_ID=s.COURSE_PERIOD_ID AND s.STUDENT_ID='".UserStudentID()."' AND cp.PERIOD_ID='".$cp_RET[1]['PERIOD_ID']."' AND s.MARKING_PERIOD_ID IN (".GetAllMP(GetMPTable(GetMP($cp_RET[1]['MARKING_PERIOD_ID'],'TABLE')),$cp_RET[1]['MARKING_PERIOD_ID']).")"));
	foreach($period_RET as $existing)
	{
		if(strlen($cp_RET[1]['DAYS'])+strlen($existing['DAYS'])>7)
			$days_conflict = true;
		else
		{
			$days_len = strlen($cp_RET[1]['DAYS']);
			for($i=0;$i<$days_len;$i++)
			{
				if(strpos($existing['DAYS'],substr($cp_RET[1]['DAYS'],$i,1))!==false)
				{
					$days_conflict = true;
					break;
				}
			}
		}			
	}
	if($days_conflict)
		$warnings[] = _('There is already a course scheduled in that period.');

	$cp_RET[1]['PREREQUISITES'] = str_replace("\n","\r",str_replace("\r\n","\r",$cp_RET[1]['PREREQUISITES']));
	if($cp_RET[1]['PREREQUISITES'])
		$cp_RET[1]['PREREQUISITES'] = explode("\r",$cp_RET[1]['PREREQUISITES']);
	else
		$cp_RET[1]['PREREQUISITES'] = array();
	if(count($cp_RET[1]['PREREQUISITES']))
	{
		$nums = '';
		foreach($cp_RET[1]['PREREQUISITES'] as $course_num)
		{
			$nums .= ",'".$course_num."'";
		}
		$nums = substr($nums,1);
		$grades_RET = DBGet(DBQuery("SELECT CARRIES_CREDITS,COURSE_NUM FROM STUDENT_REPORT_CARD_GRADES WHERE STUDENT_ID='".UserStudentID()."' AND COURSE_NUM IN (".$nums.")"),array(),array('COURSE_NUM'));
		// make sure all pre-reqs have been taken
		if(count($grades_RET)<count($cp_RET[1]['PREREQUISITES']))
			$warnings[] = _('Not all of the prerequisites have been taken.');
		// make sure all the pre-reqs have been passed
		foreach($grades_RET as $course_num=>$grades)
		{
			foreach($grades as $grade)
			{
				if($grade['CARRIES_CREDITS']!='Y')
				{
					$warnings[] = _('At least one of the prerequisites has not been completed with a passing grade.');
					break 2;
				}
			}
		}
		// note, this was copied from choose_course, below
	}

	if(!$warnings)
	{
		$message = '<TABLE>
		<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Course').':</font></TD><TD>'.$cp_RET[1]['TITLE'].' - '.$cp_RET[1]['COURSE_WEIGHT'].'</TD></TR>
		<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Period').':</font></TD><TD>'.GetPeriod($cp_RET[1]['PERIOD_ID']).'</TD></TR>
		<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Instructor').':</font></TD><TD>'.GetTeacher($cp_RET[1]['TEACHER_ID']).'</TD></TR>';
		if($cp_RET[1]['ROOM'])
			$message .= '<TR><TD align='.ALIGN_RIGHT.'><font color=gray>'._('Room').'</font></TD><TD>'.$cp_RET[1]['ROOM'].'</TD></TR>';
		$message .= '</TABLE>';
		if(Prompt(_('Confirm Add'),_('Are you sure you want to add this course to your schedule?'),$message))
		{
			DBQuery("INSERT INTO SCHEDULE (ID,SYEAR,SCHOOL_ID,STUDENT_ID,START_DATE,COURSE_ID,COURSE_WEIGHT,COURSE_PERIOD_ID,MP,MARKING_PERIOD_ID) values(".db_seq_nextval('SCHEDULE_SEQ').",'".UserSyear()."','".UserSchool()."','".UserStudentID()."','".$date."','".$_REQUEST['course_id']."','".$_REQUEST['course_weight']."','".$_REQUEST['course_period_id']."','".$cp_RET[1]['MP']."','".$cp_RET[1]['MARKING_PERIOD_ID']."')");
			DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS+1 WHERE COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'");
			echo "<script language=javascript>document.location.href = 'Modules.php?modname=".$_REQUEST['modname']."&LO_index=".$_REQUEST['LO_index']."';</script>";
		}
	}
	else
	{
		$list = '<BR>'.ErrorMessage($warnings,'error');
		$list .= '<A onclick="javascript:history.go(-1);"><IMG SRC=assets/back.gif height=15></A><A onclick="javascript:history.go(-1);"><font color=blue>'._('Back').'</font></A>';
		echo $list;
	}
}

if(UserStudentID() && $_REQUEST['modfunc']!='choose_course')
{
	$time = time();
	
	$semesters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE FROM SCHOOL_SEMESTERS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));
	$quarters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE FROM SCHOOL_QUARTERS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));

	if(!$_REQUEST['marking_period_id'] && User('PROFILE')=='student')
	{
		// IF THERE'S NO MARKING PERIOD SET, AND USER IS A STUDENT, ENTER FIRST AVAILABLE MP FOR REGISTRATION
		foreach($quarters_RET as $quarter)
		{
			if(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_START_DATE') && ($time >= strtotime(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_START_DATE')) && $time <= strtotime(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_END_DATE'))))
			{
				$_REQUEST['marking_period_id'] = $quarter['MARKING_PERIOD_ID'];
				break;
			}
		}
		foreach($semesters_RET as $quarter)
		{
			if(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_START_DATE') && ($time >= strtotime(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_START_DATE')) && $time <= strtotime(GetMP($quarter['MARKING_PERIOD_ID'],'REGISTRATION_END_DATE'))))
			{
				$_REQUEST['marking_period_id'] = $quarter['MARKING_PERIOD_ID'];
				break;
			}
		}
	}
	
	if(!$_REQUEST['marking_period_id'])
		$_REQUEST['marking_period_id'] = '0';

	if(GetMP($_REQUEST['marking_period_id'],'REGISTRATION_START_DATE') && ($time >= strtotime(GetMP($_REQUEST['marking_period_id'],'REGISTRATION_START_DATE')) && $time <= strtotime(GetMP($_REQUEST['marking_period_id'],'REGISTRATION_END_DATE'))))
		$allow_register = true;

	DrawStudentHeader();
	echo "<FORM action=Modules.php?modname=$_REQUEST[modname]&LO_index=".$_REQUEST['LO_index']."&modfunc=modify&marking_period_id=".$_REQUEST['marking_period_id']." METHOD=POST>";
	if(User('PROFILE')=='admin')
		DrawHeader(' &nbsp; &nbsp; <B>'._('Effective Date').': </B>'.PrepareDate($date,'_date').' <INPUT type=checkbox name=include_inactive value=Y'.($_REQUEST['include_inactive']=='Y'?' CHECKED':'').'>'._('Include Inactive Courses'),SubmitButton(_('Update')));
	/*
	$schedule_fields_RET = DBGet(DBQuery("SELECT cf.TITLE,s.CUSTOM_71 FROM CUSTOM_FIELDS cf,STUDENTS s WHERE s.STUDENT_ID='".UserStudentID()."' AND cf.ID='71'"));
	if($schedule_fields_RET[1]['TITLE']=='Team')
		DrawHeader('<font color=gray><b>'.$schedule_fields_RET[1]['TITLE'].': </b></font>'.$schedule_fields_RET[1]['CUSTOM_71']);
	*/
	
	$sql = "SELECT 
				s.ID,s.COURSE_ID,s.COURSE_WEIGHT,s.COURSE_PERIOD_ID,
				s.MARKING_PERIOD_ID,s.START_DATE,s.END_DATE, 
				cp.PERIOD_ID,cp.MARKING_PERIOD_ID as COURSE_MARKING_PERIOD_ID,s.MP,sp.SORT_ORDER,
				c.TITLE,cp.COURSE_PERIOD_ID AS PERIOD_PULLDOWN,
				s.STUDENT_ID,ROOM,DAYS,SCHEDULER_LOCK
			FROM SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp
			WHERE
				s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID
				AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
				AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID
				AND s.STUDENT_ID='".UserStudentID()."' 
				AND s.SYEAR='".UserSyear()."'
				AND s.SCHOOL_ID = '".UserSchool()."'";
	if($_REQUEST['marking_period_id']!='0')
		$sql .= " AND cp.MARKING_PERIOD_ID IN (".GetAllMP(false,$_REQUEST['marking_period_id']).')';

	if($_REQUEST['include_inactive']!='Y')
		$sql .= " AND ('".$date."' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND s.START_DATE<='".$date."')) ";
	$sql .= " ORDER BY sp.SORT_ORDER,s.MARKING_PERIOD_ID";
		
	$QI = DBQuery($sql);
	$schedule_RET = DBGet($QI,array('TITLE'=>'_makeTitle','PERIOD_PULLDOWN'=>'_makePeriodSelect','COURSE_MARKING_PERIOD_ID'=>'_makeMPSelect','SCHEDULER_LOCK'=>'_makeLock','START_DATE'=>'_makeDate','END_DATE'=>'_makeDate'));

	$mps_RET = DBGet(DBQuery("SELECT DISTINCT MP FROM COURSE_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
	foreach($mps_RET as $mp)
	{
		if($mp['MP']=='QTR')
			$qtr_courses = true;
		elseif($mp['MP']=='SEM')
			$sem_courses = true;
	}

	$tabs = array(array('title'=>_('Full Year'),'link'=>"Modules.php?modname=$_REQUEST[modname]&marking_period_id=0&LO_index=".$_REQUEST['LO_index']));

	if($sem_courses)
	{
		foreach($semesters_RET as $category)
			$tabs[] = array('title'=>$category['TITLE'],'link'=>"Modules.php?modname=$_REQUEST[modname]&marking_period_id=".$category['MARKING_PERIOD_ID']."&LO_index=".$_REQUEST['LO_index']);
	}
	
	if($qtr_courses)
	{
		foreach($quarters_RET as $category)
			$tabs[] = array('title'=>$category['TITLE'],'link'=>"Modules.php?modname=$_REQUEST[modname]&marking_period_id=".$category['MARKING_PERIOD_ID']."&LO_index=".$_REQUEST['LO_index']);
	}

	$options = array('search'=>false,'save'=>false);
	if(User('PROFILE')!='student')
	{
		$link['add']['link'] = "# onclick='window.open(\"Modules.php?modname=$_REQUEST[modname]&modfunc=choose_course\",\"\",\"scrollbars=yes,resizable=yes,width=800,height=400\");' ";
		$link['add']['title'] = _("Add a Course");
	}
	elseif($allow_register==true)
	{
		$link['remove']['link'] = 'Modules.php?modname='.$_REQUEST['modname'].'&modfunc=student_drop';
		$link['remove']['variables'] = array('id'=>'ID','course_period_id'=>'COURSE_PERIOD_ID','course_id'=>'COURSE_ID');
		$options['add'] = true;
	}

	$columns = array('TITLE'=>_('Course'),'PERIOD_PULLDOWN'=>_('Period - Teacher'),'ROOM'=>_('Room'),'DAYS'=>_('Meeting Days'),'COURSE_MARKING_PERIOD_ID'=>_('Term'),'SCHEDULER_LOCK'=>'<IMG SRC=assets/locked.gif border=0>','START_DATE'=>_('Enrolled'),'END_DATE'=>_('Dropped'));

	$days_RET = DBGet(DBQuery("SELECT DISTINCT DAYS FROM COURSE_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
	if(count($days_RET)==1)
		unset($columns['DAYS']);

	if($_REQUEST['_FOCUS_PDF'] || !AllowEdit())
		unset($columns['SCHEDULER_LOCK']);
	if(User('PROFILE')=='student')
	{
		unset($columns['START_DATE']);
		unset($columns['END_DATE']);
	}

	echo '<BR>';

	$_FOCUS['selected_tab'] = "Modules.php?modname=$_REQUEST[modname]&marking_period_id=$_REQUEST[marking_period_id]&LO_index=".$_REQUEST['LO_index'];
	if(count($schedule_RET)==0)
	{
		echo PopTable('header',$tabs);
		ListOutput($schedule_RET,$columns,_('Class'),_('Classes'),$link,array(),$options);
		echo '<BR><CENTER>'.SubmitButton(_('Update')).'</CENTER>';
		echo PopTable('footer');
	}
	else
	{
		if(count($tabs)>1)
			echo '<CENTER>'.WrapTabs($tabs,$_FOCUS['selected_tab']).'</CENTER>';
		ListOutput($schedule_RET,$columns,'','',$link,array(),$options);
		echo '<BR><CENTER>'.SubmitButton(_('Update')).'</CENTER>';
	}
	echo '</FORM>';

	if(User('PROFILE')=='student' && $allow_register)
	{
		echo '<FORM action=Modules.php?modname='.$_REQUEST['modname'].'&modfunc=reg_search&marking_period_id='.$_REQUEST['marking_period_id'].' method=POST>';
		$subjects_RET = DBGet(DBQuery("SELECT SUBJECT_ID,TITLE FROM COURSE_SUBJECTS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
		$subjects = '<SELECT name=subject_id><option value="">'._('All Subjects').'</option>';
		foreach($subjects_RET as $subject)
			$subjects .= "<OPTION value=$subject[SUBJECT_ID]".($_REQUEST['subject_id']==$subject['SUBJECT_ID']?' SELECTED':'').">".$subject['TITLE'].'</OPTION>';
		$subjects .= '</SELECT>';

		$periods_RET = DBGet(DBQuery("SELECT PERIOD_ID,TITLE FROM SCHOOL_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
		$periods = '<SELECT name=period_id><option value="">'._('All Periods').'</option>';
		foreach($periods_RET as $period)
			$periods .= "<OPTION value=$period[PERIOD_ID]".($_REQUEST['period_id']==$period['PERIOD_ID']?' SELECTED':'').">".$period['TITLE'].'</OPTION>';
		$periods .= '</SELECT>';

		if($_REQUEST['modfunc']=='reg_search')
		{
			$sections_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID,c.COURSE_ID,c.TITLE,w.COURSE_WEIGHT,cp.PERIOD_ID,cp.DAYS,cp.TEACHER_ID,cp.TOTAL_SEATS,cp.FILLED_SEATS,(COALESCE(cp.TOTAL_SEATS,0)-COALESCE(cp.FILLED_SEATS,0)) AS AVAILABLE_SEATS,(COALESCE(cp.TOTAL_SEATS,0)-COALESCE(cp.FILLED_SEATS,0))||' / '||COALESCE(cp.TOTAL_SEATS,0) AS SEATS,cp.ROOM FROM COURSES c,COURSE_WEIGHTS w,COURSE_PERIODS cp WHERE "
			.($_REQUEST['subject_id']?"c.SUBJECT_ID='".$_REQUEST['subject_id']."' AND ":'')
			.($_REQUEST['course_title']?"UPPER(c.TITLE) LIKE '%".strtoupper($_REQUEST['course_title'])."%' AND ":'')
			.($_REQUEST['course_num']?"UPPER(c.SHORT_NAME) LIKE '".strtoupper($_REQUEST['course_num'])."%' AND ":'')
			.($_REQUEST['period_id']?"cp.PERIOD_ID='".$_REQUEST['period_id']."' AND ":'')
			." cp.MARKING_PERIOD_ID IN (".GetAllMP(false,$_REQUEST['marking_period_id']).') AND '
			." c.COURSE_ID=w.COURSE_ID AND cp.COURSE_ID=c.COURSE_ID AND cp.COURSE_WEIGHT=w.COURSE_WEIGHT AND c.SYEAR='".UserSyear()."' AND c.SCHOOL_ID='".UserSchool()."'
			ORDER BY c.TITLE,AVAILABLE_SEATS,cp.PERIOD_ID
			"),array('PERIOD_ID'=>'GetPeriod','TEACHER_ID'=>'GetTeacher','TITLE'=>'_makeSearchTitle'));
			
			$link['TITLE']['link'] = 'Modules.php?modname='.$_REQUEST['modname'].'&modfunc=student_add&marking_period_id='.$_REQUEST['marking_period_id'];
			$link['TITLE']['variables'] = array('course_period_id'=>'COURSE_PERIOD_ID','course_id'=>'COURSE_ID','course_weight'=>'COURSE_WEIGHT');
			
			$columns = array('TITLE'=>_('Course'),'PERIOD_ID'=>_('Period'),'TEACHER_ID'=>_('Teacher'),'ROOM'=>_('Room'),'DAYS'=>_('Days'),'SEATS'=>_('Seats: Available / Total'));
			if(count($days_RET)==1)
				unset($columns['DAYS']);

			$list = ListOutputBuffered($sections_RET,$columns,_('Section'),_('Sections'),$link,array(),array('search'=>false));
		}

		if($_REQUEST['modfunc']!='student_drop' && $_REQUEST['modfunc']!='student_add')
		{
			DrawBlock(_('Course Registration: Search for a Course to Add'),_('Registration open until').' '.ProperDate(GetMP($_REQUEST['marking_period_id'],'REGISTRATION_END_DATE')).'<BR><BR><TABLE width=100% class=BoxContents>
			<TR><TD align='.ALIGN_RIGHT.'>'._('Subject').'</TD><TD>'.$subjects.'</TD>
			<TD align='.ALIGN_RIGHT.'>'._('Course Title').'</TD><TD><INPUT type=text id=course_title name=course_title value="'.$_REQUEST['course_title'].'" size=10></TD>
			<TD align='.ALIGN_RIGHT.'>'._('Course Num').'</TD><TD><INPUT type=text id=course_num name=course_num value="'.$_REQUEST['course_num'].'" size=8></TD>
			<TD align='.ALIGN_RIGHT.'>'._('Period').'</TD><TD>'.$periods.'</TD><TD><INPUT type=submit value='._('Search').'></TD></TR>
			</TABLE>'.$list);
		}
		echo '</FORM>';
	}

	if(AllowEdit() && User('PROFILE')=='admin')
	{
		unset($_REQUEST);
		$_REQUEST['modname'] = 'Scheduling/Schedule.php';
		$_REQUEST['stuid'] = UserStudentID();
		$_REQUEST['search_modfunc'] = 'list';
		$extra['link']['COURSE']['link'] = "Modules.php?modname=$_REQUEST[modname]&modfunc=choose_course";
		$extra['link']['COURSE']['variables'] = array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID','course_weight'=>'COURSE_WEIGHT');
		$extra['link']['COURSE']['js'] = true;

		$list_PHP_SELF = $_SESSION['List_PHP_SELF'];
		include($staticpath.'modules/Scheduling/UnfilledRequests.php');
		$_SESSION['List_PHP_SELF'] = $list_PHP_SELF;
	}
}

if($_REQUEST['modfunc']=='choose_course')
{
	if($_REQUEST['course_id'])
	{
		$weights_RET = DBGet(DBQuery("SELECT COURSE_WEIGHT,GPA_MULTIPLIER FROM COURSE_WEIGHTS WHERE COURSE_ID='$_REQUEST[course_id]'"));
		if(count($weights_RET)==1)
			$_REQUEST['course_weight'] = $weights_RET[1]['COURSE_WEIGHT'];
	}

	if(!$_REQUEST['course_period_id'])
		include $staticpath."modules/Scheduling/Courses.php";
	else
	{
		$min_date = DBGet(DBQuery("SELECT min(SCHOOL_DATE) AS MIN_DATE FROM ATTENDANCE_CALENDAR WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
		if($min_date[1]['MIN_DATE'] && DBDate('postgres')<$min_date[1]['MIN_DATE'])
			$date = $min_date[1]['MIN_DATE'];
		else
			$date = DBDate();
		
		$cp_RET = DBGet(DBQuery("SELECT c.PREREQUISITES,cp.MP,cp.MARKING_PERIOD_ID,cp.DAYS,cp.PERIOD_ID,cp.MARKING_PERIOD_ID,cp.TOTAL_SEATS,COALESCE(cp.FILLED_SEATS,0) AS FILLED_SEATS FROM COURSE_PERIODS cp,COURSES c WHERE c.COURSE_ID=cp.COURSE_ID AND cp.COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'"));
		if(is_numeric($cp_RET[1]['TOTAL_SEATS']) && $cp_RET[1]['TOTAL_SEATS']<=$cp_RET[1]['FILLED_SEATS'])
			$warnings[] = _('That section is already full.');
		
		$current_RET = DBGet(DBQuery("SELECT COURSE_PERIOD_ID FROM SCHEDULE WHERE STUDENT_ID='".UserStudentID()."' AND COURSE_ID='".$_REQUEST['course_id']."' AND COURSE_WEIGHT='".$_REQUEST['course_weight']."' AND (START_DATE<='".DBDate()."' OR (END_DATE IS NOT NULL AND END_DATE<='".DBDate()."'))"));
		if(count($current_RET))
			$warnings[] = _('This student is already scheduled into this course.');

		$days_conflict = false;
		$period_RET = DBGet(DBQuery("SELECT cp.DAYS FROM SCHEDULE s,COURSE_PERIODS cp WHERE cp.COURSE_PERIOD_ID=s.COURSE_PERIOD_ID AND s.STUDENT_ID='".UserStudentID()."' AND cp.PERIOD_ID='".$cp_RET[1]['PERIOD_ID']."' AND s.MARKING_PERIOD_ID IN (".GetAllMP(GetMPTable(GetMP($cp_RET[1]['MARKING_PERIOD_ID'],'TABLE')),$cp_RET[1]['MARKING_PERIOD_ID']).") AND (s.START_DATE<='".DBDate()."' AND (s.END_DATE IS NULL OR '".DBDate()."'<=s.END_DATE))"));
		foreach($period_RET as $existing)
		{
			if(strlen($cp_RET[1]['DAYS'])+strlen($existing['DAYS'])>7)
			{
				$days_conflict = true;
				break;
			}
			else
			{
				$days_len = strlen($cp_RET[1]['DAYS']);
				for($i=0;$i<$days_len;$i++)
				{
					if(strpos($existing['DAYS'],substr($cp_RET[1]['DAYS'],$i,1))!==false)
					{
						$days_conflict = true;
						break 2;
					}
				}
			}			
		}
		if($days_conflict)
			$warnings[] = _('There is already a course scheduled in that period.');
		
		$cp_RET[1]['PREREQUISITES'] = str_replace("\n","\r",str_replace("\r\n","\r",$cp_RET[1]['PREREQUISITES']));
		if($cp_RET[1]['PREREQUISITES'])
			$cp_RET[1]['PREREQUISITES'] = explode("\r",$cp_RET[1]['PREREQUISITES']);
		else
			$cp_RET[1]['PREREQUISITES'] = array();
		if(count($cp_RET[1]['PREREQUISITES']))
		{
			$nums = '';
			foreach($cp_RET[1]['PREREQUISITES'] as $course_num)
			{
				$nums .= ",'".$course_num."'";
			}
			$nums = substr($nums,1);
			$grades_RET = DBGet(DBQuery("SELECT CARRIES_CREDITS,COURSE_NUM FROM STUDENT_REPORT_CARD_GRADES WHERE STUDENT_ID='".UserStudentID()."' AND COURSE_NUM IN (".$nums.")"),array(),array('COURSE_NUM'));
			// make sure all pre-reqs have been taken
			if(count($grades_RET)<count($cp_RET[1]['PREREQUISITES']))
				$warnings[] = _('Not all of the prerequisites have been taken.');
			// make sure all the pre-reqs have been passed
			foreach($grades_RET as $course_num=>$grades)
			{
				foreach($grades as $grade)
				{
					if($grade['CARRIES_CREDITS']!='Y')
					{
						$warnings[] = _('At least one of the prerequisites has not been completed with a passing grade.');
						break 2;
					}
				}
			}
			// then, copy to student_add
		}
		
		if(!$warnings)
		{
			DBQuery("INSERT INTO SCHEDULE (ID,SYEAR,SCHOOL_ID,STUDENT_ID,START_DATE,COURSE_ID,COURSE_WEIGHT,COURSE_PERIOD_ID,MP,MARKING_PERIOD_ID) values(".db_seq_nextval('SCHEDULE_SEQ').",'".UserSyear()."','".UserSchool()."','".UserStudentID()."','".$date."','".$_REQUEST['course_id']."','".$_REQUEST['course_weight']."','".$_REQUEST['course_period_id']."','".$cp_RET[1]['MP']."','".$cp_RET[1]['MARKING_PERIOD_ID']."')");
			DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS+1 WHERE COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'");
			echo "<script language=javascript>opener.document.location = 'Modules.php?modname=".$_REQUEST['modname']."&time=".time()."&LO_index=".$_REQUEST['LO_index']."'; window.close();</script>";
		}
		elseif($warnings)
		{
			if(Prompt(_('Confirm'),_('There is a conflict. Are you sure you want to add this section?'),ErrorMessage($warnings,'note')))
			{
				DBQuery("INSERT INTO SCHEDULE (ID,SYEAR,SCHOOL_ID,STUDENT_ID,START_DATE,COURSE_ID,COURSE_WEIGHT,COURSE_PERIOD_ID,MP,MARKING_PERIOD_ID) values(".db_seq_nextval('SCHEDULE_SEQ').",'".UserSyear()."','".UserSchool()."','".UserStudentID()."','".$date."','".$_REQUEST['course_id']."','".$_REQUEST['course_weight']."','".$_REQUEST['course_period_id']."','".$cp_RET[1]['MP']."','".$cp_RET[1]['MARKING_PERIOD_ID']."')");
				DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS=FILLED_SEATS+1 WHERE COURSE_PERIOD_ID='".$_REQUEST['course_period_id']."'");
				echo "<script language=javascript>opener.document.location = 'Modules.php?modname=".$_REQUEST['modname']."&time=".time()."&LO_index=".$_REQUEST['LO_index']."'; window.close();</script>";
			}
		}
	}
}

function _makeTitle($value,$column='')
{	global $_FOCUS,$THIS_RET;

	return $value.' - '.$THIS_RET['COURSE_WEIGHT'];
}

function _makeLock($value,$column)
{	global $THIS_RET;

	if($value=='Y')
		$img = 'locked';
	else
		$img = 'unlocked';

	return '<IMG SRC=assets/'.$img.'.gif '.(AllowEdit()?'onclick="if(this.src.indexOf(\'assets/locked.gif\')!=-1) {this.src=\'assets/unlocked.gif\'; document.getElementById(\'lock'.$THIS_RET['ID'].'\').value=\'\';} else {this.src=\'assets/locked.gif\'; document.getElementById(\'lock'.$THIS_RET['ID'].'\').value=\'Y\';}"':'').'><INPUT type=hidden name=schedule['.$THIS_RET['ID'].'][SCHEDULER_LOCK] id=lock'.$THIS_RET['ID'].' value='.$value.'>';
}

function _makePeriodSelect($course_period_id,$column='')
{	global $_FOCUS,$THIS_RET,$min_date;

	$sql = "SELECT cp.COURSE_PERIOD_ID,cp.PARENT_ID,cp.TITLE,cp.MARKING_PERIOD_ID,COALESCE(cp.TOTAL_SEATS-cp.FILLED_SEATS,0) AS AVAILABLE_SEATS FROM COURSE_PERIODS cp LEFT OUTER JOIN SCHOOL_PERIODS sp ON (sp.PERIOD_ID=cp.PERIOD_ID) WHERE cp.COURSE_ID='$THIS_RET[COURSE_ID]' AND cp.COURSE_WEIGHT='$THIS_RET[COURSE_WEIGHT]' ORDER BY sp.SORT_ORDER";
	$QI = DBQuery($sql);
	$orders_RET = DBGet($QI);
	
	foreach($orders_RET as $value)
	{
		if($value['COURSE_PERIOD_ID']!=$value['PARENT_ID'])
		{
			$parent = DBGet(DBQuery("SELECT SHORT_NAME FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='".$value['PARENT_ID']."'"));
			$parent = $parent[1]['SHORT_NAME'];
		}
		$periods[$value['COURSE_PERIOD_ID']] = $value['TITLE'] . (($value['MARKING_PERIOD_ID']!=0 && $value['COURSE_PERIOD_ID']!=$course_period_id)?' ('.GetMP($value['MARKING_PERIOD_ID']).')':'').($value['COURSE_PERIOD_ID']!=$course_period_id?' ('.$value['AVAILABLE_SEATS'].' seats)':'').(($value['COURSE_PERIOD_ID']!=$course_period_id && $parent)?' -> '.$parent:'');
	}

	if(!$min_date)
	{
		$min_date = DBGet(DBQuery("SELECT min(SCHOOL_DATE) AS MIN_DATE FROM ATTENDANCE_CALENDAR WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'"));
		if($min_date[1]['MIN_DATE'] && DBDate('postgres')>=$min_date[1]['MIN_DATE'])
			return $periods[$course_period_id];
		else
			return SelectInput($course_period_id,'schedule['.$THIS_RET['ID'].'][COURSE_PERIOD_ID]','',$periods,false);
	}
	elseif($min_date[1]['MIN_DATE'] && DBDate('postgres')>=$min_date[1]['MIN_DATE'])
		return $periods[$course_period_id];
	else
		return SelectInput($course_period_id,'schedule['.$THIS_RET['ID'].'][COURSE_PERIOD_ID]','',$periods,false);
}

function _makeMPSelect($mp_id,$name='')
{	global $_FOCUS,$THIS_RET;

	if(!$_FOCUS['_makeMPSelect'])
	{
		$semesters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE,NULL AS SEMESTER_ID FROM SCHOOL_SEMESTERS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));
		$quarters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE,SEMESTER_ID FROM SCHOOL_QUARTERS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));
		
		$_FOCUS['_makeMPSelect']['0'][1] = array('MARKING_PERIOD_ID'=>'0','TITLE'=>_('Full Year'),'SEMESTER_ID'=>'');
		foreach($semesters_RET as $sem)
			$_FOCUS['_makeMPSelect']['0'][] = $sem;
		foreach($quarters_RET as $qtr)
			$_FOCUS['_makeMPSelect']['0'][] = $qtr;
		
		$quarters_QI = DBQuery("SELECT MARKING_PERIOD_ID,TITLE,SEMESTER_ID FROM SCHOOL_QUARTERS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER");
		$quarters_indexed_RET = DBGet($quarters_QI,array(),array('SEMESTER_ID'));

		foreach($semesters_RET as $sem)
		{
			$_FOCUS['_makeMPSelect'][$sem['MARKING_PERIOD_ID']][1] = $sem;
			foreach($quarters_indexed_RET[$sem['MARKING_PERIOD_ID']] as $qtr)
				$_FOCUS['_makeMPSelect'][$sem['MARKING_PERIOD_ID']][] = $qtr;
		}
		
		foreach($quarters_RET as $qtr)
			$_FOCUS['_makeMPSelect'][$qtr['MARKING_PERIOD_ID']][] = $qtr;
	}
	
	foreach($_FOCUS['_makeMPSelect'][$mp_id] as $value)
		$mps[$value['MARKING_PERIOD_ID']] = $value['TITLE'];
	
	if($THIS_RET['MARKING_PERIOD_ID']!=$mp_id)
		$mps[$THIS_RET['MARKING_PERIOD_ID']] = '* '.$mps[$THIS_RET['MARKING_PERIOD_ID']];
	
	return SelectInput($THIS_RET['MARKING_PERIOD_ID'],'schedule['.$THIS_RET['ID'].'][MARKING_PERIOD_ID]','',$mps,false);
}

function _makeDate($value,$column)
{	global $THIS_RET;

	if($column=='START_DATE')
		$allow_na = false;
	else
		$allow_na = true;

	return DateInput($value,'schedule['.$THIS_RET['ID'].']['.$column.']','','force',$allow_na);
}

function _makeSearchTitle($value,$column)
{
	return button('add').$value;
}
?>
